//---------------------------------------------------------------------
// File: EntLibDBQueryStep.cs
// 
// Summary: 
//---------------------------------------------------------------------

using System;
using System.Data;
using System.Threading;
using System.Xml;


using BizUnit.BizUnitOM;

namespace BizUnit.Extensions.EntLib
{
    using Utilities;

    /// <summary>
    /// The EntLibDBQueryStep is used to query a SQL query against a database and validate the values for the row returned.
    /// </summary>
    /// 
    /// <remarks>
    /// The following shows an example of the Xml representation of this test step.
    /// 
    /// <code escaped="true">
    ///	<TestStep assemblyPath="" typeName="BizUnit.EntLibDBQueryStep">
    ///		<DelayBeforeCheck>1</DelayBeforeCheck>
    ///		<ConfigFileLocation>..\\..\\db.config</ConnectionString>
    ///		<DatabaseConfigName>Northwind</DatabaseConfigName>
    ///		<!-- 
    ///		The SQL Query to execute is built by formatting the RawSQLQuery substituting in the 
    ///		SQLQueryParam's
    ///		-->
    ///		<SQLQuery>
    ///			<RawSQLQuery>select * from dbo.bam_Event_Completed where EventId = {0}</RawSQLQuery>
    ///			<SQLQueryParams>
    ///				<SQLQueryParam takeFromCtx="EventId"></SQLQueryParam>
    ///			</SQLQueryParams>
    ///		</SQLQuery>
    ///			
    ///		<Rows>
    ///			<Columns>
    ///			<!-- 
    ///			Note: The column names are dependant on the DB schema being checked against.
    ///			Adding the attribute isUnique="true" to one of the columns allows it to be 
    ///			uniquely selcted in the scenario where multiple rows are returned.
    ///			-->
    ///					
    ///				<EventType>Switch</EventType>
    ///				<EventStatus>Completed</EventStatus>
    ///				<ProcessorId>JVQFFCCZ0</ProcessorId>
    ///				<SchemeId>GF81300000</SchemeId>
    ///				<EventFailed>null</EventFailed>
    ///				<EventHeld>null</EventHeld>
    ///				<EventHoldNotifRec>null</EventHoldNotifRec>
    ///			</Columns>
    ///		</Rows>	
    ///	</TestStep>
    ///	</code>
    ///	
    ///	The ContextManipulator builds a new context item by appeanding the values of multiple context items
    ///	<list type="table">
    ///		<listheader>
    ///			<term>Tag</term>
    ///			<description>Description</description>
    ///		</listheader>
    ///		<item>
    ///			<term>DelayBeforeCheck</term>
    ///			<description>The number of seconds to wait before executing the step</description>
    ///		</item>
    ///		<item>
    ///			<term>ConfigFileLocation</term>
    ///			<description>Location of the Database configuration file</description>
    ///		</item>
    ///     <item>
    ///			<term>DatabaseConfigName</term>
    ///			<description>Name of the Database to be used in the configuration file</description>
    ///		</item>
    ///		<item>
    ///			<term>SQLQuery/RawSQLQuery</term>
    ///			<description>The raw SQL string that will be formatted by substituting in the SQLQueryParam</description>
    ///		</item>
    ///		<item>
    ///			<term>SQLQuery/SQLQueryParams/SQLQueryParam</term>
    ///			<description>The parameters to substitute into RawSQLQuery <para>(repeating)</para></description>
    ///		</item>
    ///		<item>
    ///			<term>Rows/Columns</term>
    ///			<description>One or more columns which represent the expected query result</description>
    ///		</item>
    ///		<item>
    ///			<term>Rows/Columns/User defined element</term>
    ///			<description>The fields that are validated in the response</description>
    ///		</item>
    ///	</list>
    ///	</remarks>

    public class EntLibDBQueryStep : ITestStep, ITestStepOM
    {
        private XmlNode queryConfig;
        private XmlNode rowCollection;
        private int delayBeforeCheck;
        private string configFileLocation;
        private string databaseConfigName;
        private SqlQuery sqlQuery;


        public int DelayBeforeCheck
        {
            get
            {
                return delayBeforeCheck;
            }
            set
            {
                delayBeforeCheck = value;
            }
        }
        public string ConfigFileLocation
        {
            get
            {
                return configFileLocation;
            }
            set
            {
                configFileLocation = value;
            }
        }
        public string DatabaseConfigName
        {
            get
            {
                return databaseConfigName;
            }
            set
            {
                databaseConfigName = value;
            }
        }
        public SqlQuery SqlQuery
        {
            get
            {
                return sqlQuery;
            }
            set
            {
                sqlQuery = value;
            }
        }
        /// <summary>
        /// ITestStep.Execute() implementation
        /// </summary>
        /// <param name='testConfig'>The Xml fragment containing the configuration for this test step</param>
        /// <param name='context'>The context for the test, this holds state that is passed beteen tests</param>
        public void Execute(XmlNode testConfig, Context context)
        {
            DelayBeforeCheck = context.ReadConfigAsInt32(testConfig, "DelayBeforeCheck");
            ConfigFileLocation = context.ReadConfigAsString(testConfig, "ConfigFileLocation");
            DatabaseConfigName = context.ReadConfigAsString(testConfig, "DatabaseConfigName");

            queryConfig = testConfig.SelectSingleNode("SQLQuery");
            this.sqlQuery = SqlQuery.BuildSQLQuery(queryConfig, context);
            rowCollection = testConfig.SelectSingleNode("Rows");


        }

        #region ITestStepOM Members

        public void Execute(Context context)
        {
            Validate(context);

            string sqlQuery = EntLibDatabaseHelper.BuildSQLQuery(queryConfig, context);

            context.LogInfo("Using database connection string from : {0}", ConfigFileLocation);
            context.LogInfo("Executing database query: {0}", sqlQuery);


            // Sleep for delay seconds...
            Thread.Sleep(DelayBeforeCheck * 1000);

            XmlNodeList bamValidationRows = rowCollection.SelectNodes("*");

            DataSet ds = FillDataSet(ConfigFileLocation, DatabaseConfigName, sqlQuery);

            DataRow row = ds.Tables[0].Rows[0];

            foreach (XmlNode bamValidationRow in bamValidationRows)
            {
                XmlNodeList bamValidationCols = bamValidationRow.SelectNodes("*");
                DataRow bamDBRow = null;

                // Get the element which has the unique flag on...
                XmlNode indexColumn = bamValidationRow.SelectSingleNode("*[@isUnique='true']");
                if (null != indexColumn)
                {
                    DataRow[] bamDBRowArray = ds.Tables[0].Select(string.Format("{0} = '{1}'", indexColumn.LocalName, indexColumn.InnerText));
                    bamDBRow = bamDBRowArray[0];
                }
                else
                {
                    bamDBRow = ds.Tables[0].Rows[0];
                }

                foreach (XmlNode bamValidationCol in bamValidationCols)
                {
                    object dbData = bamDBRow[bamValidationCol.LocalName];
                    string dbDataStringValue = "";
                    if (0 == ValidateData(dbData, bamValidationCol.InnerText, ref dbDataStringValue))
                    {
                        context.LogInfo("Validation succeeded for field: {0} equals: {1}", bamValidationCol.LocalName, dbDataStringValue);
                    }
                    else
                    {
                        throw new Exception(String.Format("Validation failed for field: {0}. Expected value: {1}, actual value: {2}", bamValidationRow.LocalName, bamValidationCol.InnerText, dbDataStringValue));
                    }
                }
            }

        }

        public void Validate(Context context)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        #endregion


        #region "private methods"
        private int ValidateData(object dbData, string targetValue, ref string dbDataStringValue)
        {
            dbDataStringValue = Convert.ToString(dbData);

            switch (dbData.GetType().ToString())
            {
                case ("System.DateTime"):
                    DateTime dbDt = (DateTime)dbData;
                    DateTime targetDT = Convert.ToDateTime(targetValue);
                    return targetDT.CompareTo(dbDt);

                case ("System.DBNull"):
                    dbDataStringValue = "null";
                    return targetValue.CompareTo("null");

                case ("System.String"):
                    dbDataStringValue = (string)dbData;
                    return targetValue.CompareTo((string)dbData);

                case ("System.Int16"):
                    Int16 dbInt16 = (Int16)dbData;
                    Int16 targetInt16 = Convert.ToInt16(targetValue);
                    return targetInt16.CompareTo(dbInt16);

                case ("System.Int32"):
                    Int32 dbInt32 = (Int32)dbData;
                    Int32 targetInt32 = Convert.ToInt32(targetValue);
                    return targetInt32.CompareTo(dbInt32);

                case ("System.Int64"):
                    Int64 dbInt64 = (Int64)dbData;
                    Int64 targetInt64 = Convert.ToInt64(targetValue);
                    return targetInt64.CompareTo(dbInt64);

                case ("System.Double"):
                    Double dbDouble = (Double)dbData;
                    Double targetDouble = Convert.ToDouble(targetValue);
                    return targetDouble.CompareTo(dbDouble);

                case ("System.Decimal"):
                    Decimal dbDecimal = (Decimal)dbData;
                    Decimal targetDecimal = Convert.ToDecimal(targetValue);
                    return targetDecimal.CompareTo(dbDecimal);

                case ("System.Boolean"):
                    Boolean dbBoolean = (Boolean)dbData;
                    Boolean targetBoolean = Convert.ToBoolean(targetValue);
                    return targetBoolean.CompareTo(dbBoolean);

                case ("System.Byte"):
                    Byte dbByte = (Byte)dbData;
                    Byte targetByte = Convert.ToByte(targetValue);
                    return targetByte.CompareTo(dbByte);

                case ("System.Char"):
                    Char dbChar = (Char)dbData;
                    Char targetChar = Convert.ToChar(targetValue);
                    return targetChar.CompareTo(dbChar);

                case ("System.SByte"):
                    SByte dbSByte = (SByte)dbData;
                    SByte targetSByte = Convert.ToSByte(targetValue);
                    return targetSByte.CompareTo(dbSByte);

                default:
                    throw new ApplicationException(string.Format("Unsupported type: {0}", dbData.GetType()));
            };
        }

        private DataSet FillDataSet(string configFileLocation, string databaseConfigName, string sqlCommand)
        {
            return EntLibDatabaseHelper.ExecuteSqlCommand(configFileLocation, databaseConfigName, sqlCommand);
        }

        #endregion

    }
}
